package com.cinyi.ormlite.dao;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.cinyi.ormlite.annotation.AnnotationNotFoundException;
import com.cinyi.ormlite.annotation.TField;
import com.cinyi.ormlite.annotation.TTable;
import com.cinyi.ormlite.core.BaseModel;
import com.cinyi.ormlite.core.TableConfig;
import com.cinyi.ormlite.core.TableConfigCache;
import com.cinyi.ormlite.util.ReflectionUtils;

/**
 * 
 * @author penghui
 *
 */
public abstract class DatabaseAdapter<T extends BaseModel<?>> {
	/**
	 * t
	 */
	public static final String TABLE_ALIAS = "t";
	
	public static final int ACT_QRY = 0 ;
	public static final int ACT_NEW = 1 ;
	public static final int ACT_MOD = 2 ;
	public static final int ACT_DEL = 3 ;
	
	public SQLModel<T> buildInsertSQL(T t) throws Exception{
		Class<?> clazz = t.getClass();
		
		TableConfig cfg = TableConfigCache.getConfig(clazz);
		String tableName = cfg.getTableName();
		StringBuilder fieldNames = new StringBuilder();		//
		List<Object> fieldValues = new ArrayList<Object>();	//
		StringBuilder placeholders = new StringBuilder();	//
		Field[] fields = cfg.getFields();
		for (Field field : fields) {
//			PropertyDescriptor pd = new PropertyDescriptor(field.getName(),t.getClass());
			if(field.isAnnotationPresent(TField.class)) {
//				Object val = pd.getReadMethod().invoke(t) ;
				Object val = ReflectionUtils.getFieldValue(t, field.getName());
				if(val == null){
					continue ;
				}
				
				TField tfield = field.getAnnotation(TField.class);
				if(tfield.constrains().primaryKey() && tfield.constrains().auto()){
					continue ;
				}
				
				String name  = field.getAnnotation(TField.class).name();
				fieldNames.append(name).append(",");
				fieldValues.add(val);
				placeholders.append("?").append(",");
			}
		}
		fieldNames.deleteCharAt(fieldNames.length()-1);
		placeholders.deleteCharAt(placeholders.length()-1);
		
		StringBuilder sql = new StringBuilder("");
			sql.append("INSERT INTO ").append(tableName)
			   .append(" (").append(fieldNames.toString())
			   .append(") VALUES (").append(placeholders).append(")") ;
		
		
		SQLModel<T> result = new SQLModel<T>();
		result.setClazz(clazz);
		result.setAction(ACT_NEW);
		result.setSql( sql.toString());
		result.setData(fieldValues);
		
		return result ;
	}
	
	/**
	 * 
	 * @param t
	 * @param ignoreNull	ignore when the value is null
	 * @return
	 * @throws Exception
	 */
	public SQLModel<T> buildUpdateSQL(T t,boolean ignoreNull) throws Exception{
		Class<?> clazz = t.getClass();
		TableConfig cfg = TableConfigCache.getConfig(clazz);
		String tableName = cfg.getTableName();
		List<Object> fieldNames = new ArrayList<Object>();	//
		List<Object> fieldValues = new ArrayList<Object>();	//
		List<String> placeholders = new ArrayList<String>();//
		String idFieldName = "";
		Object idFieldValue = "";
		Field[] fields = cfg.getFields();
		for (Field field : fields) {
//			PropertyDescriptor pd = new PropertyDescriptor(field.getName(),t.getClass());
			if(field.isAnnotationPresent(TField.class)) {
				TField tfield = field.getAnnotation(TField.class);
				if(tfield.constrains().primaryKey()){
					idFieldName = tfield.name();
//					idFieldValue = pd.getReadMethod().invoke(t) ;
					idFieldValue = ReflectionUtils.getFieldValue(t, field.getName());
					continue ;
				}
				
//				Object val = pd.getReadMethod().invoke(t) ;
				Object val = ReflectionUtils.getFieldValue(t, field.getName());
				if(ignoreNull && val ==null){
					continue ;
				}
				
				fieldNames.add(field.getAnnotation(TField.class).name());
				fieldValues.add(val);
				placeholders.add("?");
			}
		}
		fieldNames.add(idFieldName);
		fieldValues.add(idFieldValue);
		placeholders.add("?");
		
		StringBuilder sql = new StringBuilder("");
		sql.append("UPDATE ").append(tableName).append(" SET ");
		int index = fieldNames.size() - 1;
		for (int i = 0; i < index; i++) {
			sql.append(fieldNames.get(i)).append("=").append(placeholders.get(i)).append(",");
		}
		sql.deleteCharAt(sql.length()-1).append(" WHERE ").append(fieldNames.get(index)).append("=").append("?");
		
		SQLModel<T> result = new SQLModel<T>();
		result.setClazz(clazz);
		result.setAction(ACT_MOD);
		result.setSql( sql.toString());
		result.setData(fieldValues);
		
		return result ;
	}
	
	public SQLModel<T> buildDeleteSQL(Object id,Class<T> clazz) throws Exception{
		TableConfig cfg = TableConfigCache.getConfig(clazz);
		List<Object> fieldValues = new ArrayList<Object>();	//
		fieldValues.add(id);
		
		String tableName = cfg.getTableName();
		String idFieldName = cfg.getIdColumnName();
		if (idFieldName == null ) {
			throw new AnnotationNotFoundException(clazz.getName() + " object not found id property.");
		}
		
		if (id == null || "".equals(id.toString().trim()) ) {
			throw new Exception(" the value ID set incorrectly.");
		}
		
		StringBuffer sql = new StringBuffer().append("DELETE FROM ").append( tableName ).append( " WHERE ").append(idFieldName ).append("=?");
		SQLModel<T> result = new SQLModel<T>();
		result.setClazz(clazz);
		result.setAction(ACT_DEL);
		result.setSql( sql.toString());
		result.setData(fieldValues);
		
		return result ;
	}
	
	/**
	 * not implements
	 * @param t
	 * @return
	 * @throws Exception
	 */
	public SQLModel<T> buildDeleteSQL(T t) throws Exception{
		SQLModel<T> result = new SQLModel<T>();
		result.setAction(ACT_DEL);
		result.setClazz(t.getClass());
		return result ;
	}
	
	public SQLModel<T> buildQuerySQL(Object idValue,Class<T> clazz) throws Exception {
		TableConfig cfg = TableConfigCache.getConfig(clazz);
		List<Object> fieldValues = new ArrayList<Object>();	//
		fieldValues.add(idValue);
		
		String tableName = cfg.getTableName();
		String idFieldName = cfg.getIdColumnName();
		if (idFieldName == null ) {
			throw new AnnotationNotFoundException(clazz.getName() + " object not found id property.");
		}
		
		if (idValue == null || "".equals(idValue.toString().trim()) ) {
			throw new Exception(" the value ID set incorrectly.");
		}
		
		String selection = buildSelectionSQL(cfg.getFields());
		
		StringBuffer sql = new StringBuffer().append(" SELECT ").append(selection).append(" FROM " ).append(tableName ).append(" " ).append(TABLE_ALIAS);
		sql.append( " WHERE ").append(idFieldName ).append("=?");
		SQLModel<T> result = new SQLModel<T>();
		result.setAction(ACT_QRY);
		result.setSql(sql.toString());
		result.setData(fieldValues);
		result.setClazz(clazz);
		return result ;
		
	}

	public SQLModel<T> buildQuerySQL(T t) throws Exception {
		if ( t == null ) return null;
		
		TableConfig cfg = TableConfigCache.getConfig(t.getClass());
		String tableName = cfg.getTableName();

		String selection = buildSelectionSQL(t);
		SQLModel<T> result = buildWhereSQL(t);
		
		StringBuffer sql = new StringBuffer().append(" SELECT ").append(selection).append(" FROM " ).append(tableName ).append(" " ).append(TABLE_ALIAS);
		if(result != null){
			sql.append(result.getSql());
		}
		if(t.getOrderby() != null){
		    sql.append(t.getOrderby()) ;
		}
		
		result.setAction(ACT_QRY);
		result.setSql(sql.toString());
		result.setClazz(t.getClass());
		
		return result ;
	}
	
	public SQLModel<T> buildQuerySQL(Class<T> clazz,String sql,List<Object> params,int page , int rows) throws Exception {
		SQLModel<T> result = new SQLModel<T>();
		result.setAction(ACT_QRY);
		result.setSql(sql);
		result.setData(params);
		result.setPageIndex(page);
		result.setRows(rows);
		result.setClazz(clazz);
		return result;
	}
	
	public SQLModel<T> buildQuerySQL(Map<String,Object> sqlWhereMap,Class<T> clazz) throws Exception {
		TableConfig cfg = TableConfigCache.getConfig(clazz);
		String tableName = cfg.getTableName();
		String selection = buildSelectionSQL(cfg.getFields());
		StringBuffer sql = new StringBuffer().append("SELECT ").append(selection).append(" FROM ").append(tableName).append( " " + TABLE_ALIAS);
		SQLModel<T> result = buildWhereSQL(sqlWhereMap); 
		if (result != null) {
			sql.append(result.getSql());
		} 
		
		result.setSql(sql.toString());
		result.setClazz(clazz);
		
		return result ;
	}
	
	public SQLModel<T> buildCountSQL(T t) throws Exception {
		if ( t == null ) return null;
		
		TableConfig cfg = TableConfigCache.getConfig(t.getClass());
		String tableName = cfg.getTableName();

		StringBuffer sql = new StringBuffer().append("SELECT count(1) as total FROM ").append(tableName).append( " " + TABLE_ALIAS);
		
		SQLModel<T> result = buildWhereSQL(t);
		if(result != null){
			sql.append(result.getSql());
			result.setSql(sql.toString());
		}
		result.setClazz(t.getClass());
		return result ;
	}
	
	public SQLModel<T> buildCountSQL(Map<String,Object> sqlWhereMap,Class<T> clazz) throws Exception {
		TableConfig cfg = TableConfigCache.getConfig(clazz);
		String tableName = cfg.getTableName();
		
		StringBuffer sql = new StringBuffer().append("SELECT count(1) as total FROM ").append(tableName).append( " " + TABLE_ALIAS);
		SQLModel<T> result = buildWhereSQL(sqlWhereMap); 
		if (result != null) {
			sql.append(result.getSql());
		} 
		
		result.setSql(sql.toString());
		result.setClazz(clazz);
		
		return result ;
	}
	
	public SQLModel<T> buildWhereSQL(T t) throws Exception {
		TableConfig cfg = TableConfigCache.getConfig(t.getClass());
		Field[] fields = cfg.getFields();
		List<Object> fieldValues = new ArrayList<Object>();
		StringBuffer sqlWhere = new StringBuffer(" WHERE ");
		boolean nullObj = true ;
		for (Field field : fields) {
			if (!field.isAnnotationPresent(TField.class)) {
				continue ;
			}
			
			String propertyName = field.getName();
			
//			PropertyDescriptor pd = new PropertyDescriptor(propertyName,t.getClass());
//			Object val = pd.getReadMethod().invoke(t) ;
			Object val = ReflectionUtils.getFieldValue(t, field.getName());
			if(val == null){
				continue ;
			}
			nullObj = false ;
			if (val.getClass() == String.class) {
				sqlWhere.append(TABLE_ALIAS + "." + field.getAnnotation(TField.class).name()).append(" LIKE ").append("?").append(" AND ");
			} else {
				sqlWhere.append(propertyName).append("=").append("?").append(" AND ");
			}
			fieldValues.add(val);
		}
		
		if(nullObj){
			return null ;
		}
		
		int pos = sqlWhere.lastIndexOf("AND");
		if(pos > -1){
			sqlWhere.delete(pos, sqlWhere.length());
		}
		
		SQLModel<T> result = new SQLModel<T>();
		result.setSql(sqlWhere.toString());
		result.setData(fieldValues);
		result.setAction(ACT_QRY);
		result.setClazz(t.getClass());
		
		return result;
		
	}
	
	/**
	 * 根据条件，返回sql条件和条件中占位符的值
	 * @param sqlWhereMap key：字段名 value：字段值
	 * @return 第一个元素为SQL条件，第二个元素为SQL条件中占位符的值
	 */
	public SQLModel<T> buildWhereSQL(Map<String,Object> sqlWhereMap) {
		if (sqlWhereMap.size() < 1 ) return null;
		
		List<Object> fieldValues = new ArrayList<Object>();
		StringBuffer sqlWhere = new StringBuffer(" WHERE ");
		Set<Entry<String, Object>> entrySets = sqlWhereMap.entrySet();
		
		boolean hasWhere = false ;
		for (Iterator<Entry<String, Object>> iteraotr = entrySets.iterator();iteraotr.hasNext();) {
			Entry<String, Object> entrySet = iteraotr.next();
			Object value = entrySet.getValue();
			
			if(value == null) continue ;
			
			if (value.getClass() == String.class) {
			    sqlWhere.append(entrySet.getKey()).append(" LIKE ").append("?").append(" AND ");
			} else {
				sqlWhere.append(entrySet.getKey()).append("=").append("?").append(" AND ");
			}
			fieldValues.add(value);
			hasWhere = true ;
		}
		if(hasWhere)
			sqlWhere.delete(sqlWhere.lastIndexOf("AND"), sqlWhere.length());
		
		SQLModel<T> result = new SQLModel<T>();
		result.setSql(sqlWhere.toString());
		result.setData(fieldValues);
		result.setAction(ACT_QRY);
		
		return result;
	}
	
	public String buildSelectionSQL(T t) throws AnnotationNotFoundException{
		TableConfig cfg = TableConfigCache.getConfig(t.getClass());
		return buildSelectionSQL(cfg.getFields());
	}

	public String buildSelectionSQL(Field[] fields) throws AnnotationNotFoundException{
		StringBuffer selection = new StringBuffer();
		for (Field field : fields) {
			String propertyName = field.getName();
			if (field.isAnnotationPresent(TField.class)) {
				TField tfield = field.getAnnotation(TField.class);
				selection.append(TABLE_ALIAS + "." + tfield.name())
						  .append(" as ").append(propertyName).append(",");
			}
		}
		selection.deleteCharAt(selection.length()-1);
		
		return selection.toString();
	}
	
	public String buildDropTableSQL(Class<?> clazz) throws Exception {
		if(!clazz.isAnnotationPresent(TTable.class)){
			throw new NullPointerException("the table not found:" + clazz.getSimpleName());
		}
		TableConfig cfg = TableConfigCache.getConfig(clazz);
		String tableName = cfg.getTableName();
		return "DROP TABLE `"+ tableName +"` \n";
	}
	
	public String buildClearDataSQL(Class<?> clazz) throws Exception {
		if(!clazz.isAnnotationPresent(TTable.class)){
			throw new NullPointerException("the table not found:" + clazz.getSimpleName());
		}
		TableConfig cfg = TableConfigCache.getConfig(clazz);
		String tableName = cfg.getTableName();
		return "DELETE FROM `"+ tableName +"` \n";
	}
	
	public abstract Integer executeSQL(SQLModel<T> model) throws Exception ;

    public abstract List<T> executeQuery(SQLModel<T> model) throws Exception ;

    public abstract Long count(SQLModel<T> model) throws Exception ;
    
}
